#pragma once

#include<iostream>
#include<vector>
#include <memory>
#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <string>
#include <ctime>
#include <stdexcept>
struct User
{
    int id;
    std::string name;
    int age;
    std::string email;
    std::string password;
    std::string phone;
};

// 用户评论结构
struct UserComment {
    int id;
    int user_id;
    std::string content;
    time_t created_at;
};

// 用户借阅记录结构
struct UserBorrowRecord {
    int id;
    int user_id;
    int book_id;
    time_t borrow_date;
    time_t return_date;
};

class UserMapper {
private:
    sql::mysql::MySQL_Driver* driver;
    std::unique_ptr<sql::Connection> con;
    
    // 时间戳转换为 time_t
    time_t sqlTimestampToTimeT(const sql::SQLString& timestamp) {
        if (timestamp.length() == 0) return 0;
        
        struct tm timeInfo = {};
        sscanf(timestamp.c_str(), "%d-%d-%d %d:%d:%d", 
               &timeInfo.tm_year, &timeInfo.tm_mon, &timeInfo.tm_mday,
               &timeInfo.tm_hour, &timeInfo.tm_min, &timeInfo.tm_sec);
        
        timeInfo.tm_year -= 1900; // tm_year 从1900年开始
        timeInfo.tm_mon -= 1;     // tm_mon 从0开始（1月为0）
        
        return mktime(&timeInfo);
    }
    
    // time_t 转换为 SQL 时间字符串
    std::string timeTToSqlTimestamp(time_t t) {
        if (t <= 0) return "NULL";
        
        char buffer[30];
        struct tm* timeInfo = localtime(&t);
        strftime(buffer, sizeof(buffer), "%Y-%m-%d %H:%M:%S", timeInfo);
        
        return std::string(buffer);
    }
    
    // time_t 转换为 SQL 日期字符串
    std::string timeTToSqlDate(time_t t) {
        if (t <= 0) return "NULL";
        
        char buffer[30];
        struct tm* timeInfo = localtime(&t);
        strftime(buffer, sizeof(buffer), "%Y-%m-%d", timeInfo);
        
        return std::string(buffer);
    }

public:
    // 构造函数 - 初始化数据库连接
    UserMapper(const std::string& host, const std::string& user, 
               const std::string& password, const std::string& database) 
    {
        try {
            driver = sql::mysql::get_mysql_driver_instance();
            con.reset(driver->connect(host, user, password));
            con->setSchema(database);
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Database connection failed: " + std::string(e.what()));
        }
    }
    
    // === 用户表操作 ===
    
    // 1. 创建新用户
    int createUser(const User& user) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "INSERT INTO users (name, age, email, password, phone) "
                    "VALUES (?, ?, ?, ?, ?)"
                )
            );
            
            pstmt->setString(1, user.name);
            user.age > 0 ? pstmt->setInt(2, user.age) : pstmt->setNull(2, sql::DataType::SMALLINT);
            pstmt->setString(3, user.email);
            pstmt->setString(4, user.password);
            user.phone.empty() ? pstmt->setNull(5, sql::DataType::VARCHAR) : pstmt->setString(5, user.phone);
            
            pstmt->executeUpdate();
            
            // 获取自增ID
            std::unique_ptr<sql::Statement> stmt(con->createStatement());
            std::unique_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT LAST_INSERT_ID()"));
            
            if (res->next()) {
                return res->getInt(1);
            }
            return -1;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Create user failed: " + std::string(e.what()));
        }
    }
    
    // 2. 通过ID获取用户
    std::unique_ptr<User> getUserById(int id) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "SELECT id, name, age, email, password, phone, created_at, updated_at "
                    "FROM users WHERE id = ?"
                )
            );
            
            pstmt->setInt(1, id);
            std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
            
            if (res->next()) {
                std::unique_ptr<User> user(new User());
                user->id = res->getInt("id");
                user->name = res->getString("name");
                user->age = res->isNull("age") ? 0 : res->getInt("age");
                user->email = res->getString("email");
                user->password = res->getString("password");
                user->phone = res->isNull("phone") ? "" : res->getString("phone");
                return user;
            }
            return nullptr;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Get user by ID failed: " + std::string(e.what()));
        }
    }
    
    // 3. 通过邮箱获取用户
    std::unique_ptr<User> getUserByEmail(const std::string& email) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "SELECT id, name, age, email, password, phone, created_at, updated_at "
                    "FROM users WHERE email = ?"
                )
            );
            
            pstmt->setString(1, email);
            std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
            
            if (res->next()) {
                std::unique_ptr<User> user(new User());
                user->id = res->getInt("id");
                user->name = res->getString("name");
                user->age = res->isNull("age") ? 0 : res->getInt("age");
                user->email = res->getString("email");
                user->password = res->getString("password");
                user->phone = res->isNull("phone") ? "" : res->getString("phone");
                return user;
            }
            return nullptr;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Get user by email failed: " + std::string(e.what()));
        }
    }
    
    // 4. 更新用户信息
    bool updateUser(const User& user) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "UPDATE users SET "
                    "name = ?, age = ?, email = ?, password = ?, phone = ? "
                    "WHERE id = ?"
                )
            );
            
            pstmt->setString(1, user.name);
            user.age > 0 ? pstmt->setInt(2, user.age) : pstmt->setNull(2, sql::DataType::SMALLINT);
            pstmt->setString(3, user.email);
            pstmt->setString(4, user.password);
            user.phone.empty() ? pstmt->setNull(5, sql::DataType::VARCHAR) : pstmt->setString(5, user.phone);
            pstmt->setInt(6, user.id);
            
            return pstmt->executeUpdate() > 0;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Update user failed: " + std::string(e.what()));
        }
    }
    
    // 5. 删除用户
    bool deleteUser(int id) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "DELETE FROM users WHERE id = ?"
                )
            );
            
            pstmt->setInt(1, id);
            return pstmt->executeUpdate() > 0;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Delete user failed: " + std::string(e.what()));
        }
    }
    
    // === 用户评论操作 ===
    
    // 1. 添加用户评论
    int addUserComment(const UserComment& comment) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "INSERT INTO user_comments (user_id, comment) "
                    "VALUES (?, ?)"
                )
            );
            
            pstmt->setInt(1, comment.user_id);
            pstmt->setString(2, comment.content);
            
            pstmt->executeUpdate();
            
            // 获取自增ID
            std::unique_ptr<sql::Statement> stmt(con->createStatement());
            std::unique_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT LAST_INSERT_ID()"));
            
            if (res->next()) {
                return res->getInt(1);
            }
            return -1;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Add comment failed: " + std::string(e.what()));
        }
    }
    
    // 2. 获取用户的所有评论
    std::vector<UserComment> getUserComments(int userId) {
        std::vector<UserComment> comments;
        
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "SELECT id, user_id, comment, created_at "
                    "FROM user_comments WHERE user_id = ? "
                    "ORDER BY created_at DESC"
                )
            );
            
            pstmt->setInt(1, userId);
            std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
            
            while (res->next()) {
                UserComment comment;
                comment.id = res->getInt("id");
                comment.user_id = res->getInt("user_id");
                comment.content = res->getString("comment");
                comment.created_at = sqlTimestampToTimeT(res->getString("created_at"));
                comments.push_back(comment);
            }
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Get comments failed: " + std::string(e.what()));
        }
        
        return comments;
    }
    
    // 3. 删除用户评论
    bool deleteUserComment(int commentId) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "DELETE FROM user_comments WHERE id = ?"
                )
            );
            
            pstmt->setInt(1, commentId);
            return pstmt->executeUpdate() > 0;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Delete comment failed: " + std::string(e.what()));
        }
    }
    
    // === 用户借阅操作 ===
    
    // 1. 添加借阅记录
    int addBorrowRecord(const UserBorrowRecord& record) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "INSERT INTO user_borrowed_books (user_id, book_id, borrow_date) "
                    "VALUES (?, ?, ?)"
                )
            );
            
            pstmt->setInt(1, record.user_id);
            pstmt->setInt(2, record.book_id);
            
            if (record.borrow_date > 0) {
                pstmt->setString(3, timeTToSqlDate(record.borrow_date));
            } else {
                pstmt->setNull(3, sql::DataType::DATE);
            }
            
            pstmt->executeUpdate();
            
            // 获取自增ID
            std::unique_ptr<sql::Statement> stmt(con->createStatement());
            std::unique_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT LAST_INSERT_ID()"));
            
            if (res->next()) {
                return res->getInt(1);
            }
            return -1;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Add borrow record failed: " + std::string(e.what()));
        }
    }
    
    // 2. 更新借阅记录（归还书籍）
    bool returnBook(int borrowId, time_t returnDate = 0) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "UPDATE user_borrowed_books "
                    "SET return_date = ? "
                    "WHERE id = ?"
                )
            );
            
            if (returnDate > 0) {
                pstmt->setString(1, timeTToSqlDate(returnDate));
            } else {
                pstmt->setNull(1, sql::DataType::DATE);
            }
            
            pstmt->setInt(2, borrowId);
            return pstmt->executeUpdate() > 0;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Return book failed: " + std::string(e.what()));
        }
    }
    
    // 3. 获取用户借阅记录
    std::vector<UserBorrowRecord> getUserBorrowRecords(int userId) {
        std::vector<UserBorrowRecord> records;
        
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "SELECT id, user_id, book_id, borrow_date, return_date "
                    "FROM user_borrowed_books WHERE user_id = ? "
                    "ORDER BY borrow_date DESC"
                )
            );
            
            pstmt->setInt(1, userId);
            std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
            
            while (res->next()) {
                UserBorrowRecord record;
                record.id = res->getInt("id");
                record.user_id = res->getInt("user_id");
                record.book_id = res->getInt("book_id");
                record.borrow_date = sqlTimestampToTimeT(res->getString("borrow_date"));
                record.return_date = sqlTimestampToTimeT(res->getString("return_date"));
                records.push_back(record);
            }
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Get borrow records failed: " + std::string(e.what()));
        }
        
        return records;
    }
    
    // 4. 删除借阅记录
    bool deleteBorrowRecord(int borrowId) {
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "DELETE FROM user_borrowed_books WHERE id = ?"
                )
            );
            
            pstmt->setInt(1, borrowId);
            return pstmt->executeUpdate() > 0;
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Delete borrow record failed: " + std::string(e.what()));
        }
    }
    
    // 5. 获取所有正在借阅的记录（未归还）
    std::vector<UserBorrowRecord> getActiveBorrows(int userId) {
        std::vector<UserBorrowRecord> records;
        
        try {
            std::unique_ptr<sql::PreparedStatement> pstmt(
                con->prepareStatement(
                    "SELECT id, user_id, book_id, borrow_date, return_date "
                    "FROM user_borrowed_books "
                    "WHERE user_id = ? AND return_date IS NULL "
                    "ORDER BY borrow_date DESC"
                )
            );
            
            pstmt->setInt(1, userId);
            std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
            
            while (res->next()) {
                UserBorrowRecord record;
                record.id = res->getInt("id");
                record.user_id = res->getInt("user_id");
                record.book_id = res->getInt("book_id");
                record.borrow_date = sqlTimestampToTimeT(res->getString("borrow_date"));
                record.return_date = 0;
                records.push_back(record);
            }
        } catch(sql::SQLException &e) {
            throw std::runtime_error("Get active borrows failed: " + std::string(e.what()));
        }
        
        return records;
    }
};
